/********************************************************************** 
 * FILE		: SqlUtilTools.java					
 *			
 * AUTHOR   : kloyf	
 * 
 * DATE		: 2014-4-14
 * 									
 * REASON	: CREATE  
 *
 ***********************************************************************/
package com.common.util.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.common.util.file.properties.PropertiesUtilTools;
import com.common.util.frame.hibernate.page.Page;
import com.common.util.sql.model.ColumnModel;
import com.common.util.string.StringUtilTools;

/**********************************************************************
 * @author kloyf
 * 
 * @version 1.0
 * 
 ***********************************************************************/
public class SqlUtilTools extends AbstractSqlUtil {
	private static Class clazz = SqlUtilTools.class;
	private static ResultSet rs = null;
	private static PreparedStatement ps = null;
	private static Statement st = null;

	private static String url = null;
	private static String userName = null;
	private static String password = null;

	/**
	 * <pre>
	 * 获得一个Connection对象。</br>
	 * 根据一个properties文件里的url，username，password，取得一个Connection对象。</br>
	 * 需要注意的是，此方法需要设置数据库驱动名。SqlUtilDefult类中已经设置好了常用数据库的驱动名，可以直接使用。
	 * </pre>
	 * 
	 * @param filePath
	 *            properties文件路径
	 * @param drivername
	 *            数据库驱动名
	 * @return Connection 返回一个数据库Connection对象。
	 * @throws SQLException
	 */
	public static Connection getCon(String filePath, String drivername) {
		Connection ct = null;
		try {
			getPramert(filePath);
			Class.forName(drivername);
			ct = DriverManager.getConnection(url, userName, password);// 注意配置文件
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ct;// 谁调用谁拿到Connection
	}

	/**
	 * <pre>
	 * 获得一个Connection对象。</br>
	 * 根据url，drivername,username，password，取得一个Connection对象。</br>
	 * 需要注意的是，此方法需要设置数据库驱动名。SqlUtilDefult类中已经设置好了常用数据库的驱动名，可以直接使用。
	 * </pre>
	 * 
	 * @param url
	 *            数据库url
	 * @param drivername
	 *            数据库驱动名
	 * @param username
	 *            数据库用户名
	 * @param password
	 *            数据库密码
	 * @return
	 */
	public static Connection getCon(String url, String drivername, String username, String password) {
		Connection ct = null;
		try {
			Class.forName(drivername);
			ct = DriverManager.getConnection(url, username, password);// 注意配置文件
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return ct;// 谁调用谁拿到Connection
	}

	/**
	 * <pre>
	 * 使用sql语句，查询出一个结果集。</br>
	 * 此方法可以在sql语句中使用占位符来进行传参。并且返回的结果集已经重新封装成List<Map<String,Object>>格式。
	 * </pre>
	 * 
	 * @param con
	 *            查询中使用的Connection对象。
	 * @param sql
	 *            sql语句。
	 * @param values
	 *            不定参数。根据sql中的占位符的数量决定参数的数量。
	 * @return List<Map<String, Object>> 返回的结果集。
	 */
	public static List<Map<String, Object>> executeQuery(Connection con, String sql, Object... values) {
		try {
			ps = con.prepareStatement(sql);
			// 给sql问号赋值
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
			rs = ps.executeQuery();

			return changeResult(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			close(rs, ps, con);
		}
	}

	/**
	 * <pre>
	 * 执行sql批量操作</br>
	 * 此方法可以进行sql的批量操作。sql语句中可以使用占位符，但需要传递一个List<Object[]>类型的参数。</br>
	 * Object[]中为sql语句中需要的参数，请与占位符一一对应。
	 * </pre>
	 * 
	 * @param con
	 *            查询中使用的Connection对象。
	 * @param sql
	 *            sql语句。
	 * @param list
	 *            操作参数。
	 * @return int 返回执行成功的记录条数。
	 */
	public static int executeBatch(Connection con, String sql, List<Object[]> list) {
		int count = 0;
		try {
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql);
			// 参数List的size大于0的时候才进行添加参数，并做批处理。
			if (list.size() > 0) {
				for (Object[] objects : list) {
					for (int i = 0; i < objects.length; i++) {
						ps.setObject(i + 1, objects[i]);
					}
					ps.addBatch();
				}
			} else {
				ps.addBatch();
			}
			// sql批执行。
			int[] counts = ps.executeBatch();
			con.commit();
			for (int i = 0; i < counts.length; i++) {
				count += counts[i];
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
		return count;
	}

	/**
	 * <pre>
	 * 使用sql语句，执行增、删、改一条记录。</br>
	 * 此方法可以在sql语句中使用点位符来进行传参。</br>
	 * 返回结果为boolean。
	 * </pre>
	 * 
	 * @param con
	 *            查询中使用的Connection对象。
	 * @param sql
	 *            sql语句。
	 * @param values
	 *            不定参数。根据sql中的占位符的数量决定参数的数量。
	 */
	public static void execute(Connection con, String sql, Object... values) {
		try {
			ps = con.prepareStatement(sql);
			if (values.length > 1) {
				for (int i = 0; i < values.length; i++) {
					ps.setObject(i + 1, values[i]);
				}
			}
			System.out.println(sql);
			ps.execute();
		} catch (SQLException e) {
			info(clazz, "sql : " + sql);
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
	}

	/**
	 * <pre>
	 * 使用sql语句，查询出一个结果集。</br>
	 * 此方法为不带参数进行查询。返回的结果集已经重新封装成List<Map<String,Object>>格式。
	 * </pre>
	 * 
	 * @param con
	 *            查询中使用的Connection对象。
	 * @param sql
	 *            sql语句。
	 * @return List<Map<String, Object>> 返回的结果集。
	 */
	public static List<Map<String, Object>> executeQuery(Connection con, String sql) {
		Statement stmt = null;
		try {
			// 得到连接
			stmt = con.createStatement();
			// 创建结果集
			rs = stmt.executeQuery(sql);
			// 将结果集返回
			return changeResult(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stmt, con);
		}
		return null;
	}

	/**
	 * <pre>
	 * 根据表名返回表格中列的信息。
	 * </pre>
	 * 
	 * @param con
	 *            数据库Connection对象。
	 * @param tableName
	 *            数据库表名。
	 * @return 返回一个List<ColumnModel>结果集。
	 */
	public static List<ColumnModel> getColumnModel(Connection con, String tableName) {
		List<ColumnModel> list = new ArrayList<ColumnModel>();

		String sql = StringUtilTools.StringFormat("SELECT * FROM {0}", tableName);
		info(clazz, "sql = " + sql);
		try {
			ps = con.prepareStatement(sql);

			ResultSetMetaData rmd = ps.getMetaData();

			ResultSet primaryKeyRs = con.getMetaData().getPrimaryKeys(con.getCatalog(), null, tableName);
			//
			String primaryKey = new String();

			while (primaryKeyRs.next()) {
				primaryKey = primaryKeyRs.getString(4);
			}
			int columnSize = rmd.getColumnCount();
			for (int i = 1; i <= columnSize; i++) {
				ColumnModel columnModel = new ColumnModel();
				if (primaryKey.equals(rmd.getColumnName(i))) {
					columnModel.setPrimaryKey(true);
				}else{
					columnModel.setPrimaryKey(false);
				}
				columnModel.setColumnName(rmd.getColumnName(i));
				columnModel.setColumnType(rmd.getColumnClassName(i));
				columnModel.setAutoIncrement(rmd.isAutoIncrement(i));
				columnModel.setColumnSize(rmd.getPrecision(i));
				// 列是否能为空。
				boolean isNull = true;
				if (rmd.isNullable(i) == ResultSetMetaData.columnNoNulls) {
					isNull = false;
				}
				columnModel.setNullable(isNull);

				list.add(columnModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * <pre>
	 * 根据一个Connection对象，获得数据库中所有表名。
	 * </pre>
	 * 
	 * @param con
	 *            数据库连接对象。
	 * @return 当前Connection连接的数据库中所有表名。
	 */
	public static List<String> getTablesName(Connection con) {
		List<String> list = new ArrayList<String>();
		try {
			DatabaseMetaData dmd = con.getMetaData();
			ResultSet rs = dmd.getTables(null, null, null, new String[] { "TABLE" });
			while (rs.next()) {
				list.add(rs.getString(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, null, con);
		}
		return list;
	}

	/**
	 * <pre>
	 * 根据一个Connection对象和表名，获得表中所有列名。
	 * </pre>
	 * 
	 * @param con
	 *            数据库连接对象。
	 * @param tableName
	 *            数据库表名。
	 * @return 返回一张表里的所有字段。
	 */
	public static List<String> getColumnsName(Connection con, String tableName) {
		List<String> list = new ArrayList<String>();
		String sql = "select * from {0}";
		sql = StringUtilTools.StringFormat(sql, tableName);
		try {
			ps = con.prepareStatement(sql);
			ResultSetMetaData rmd = ps.getMetaData();

			int columnSize = rmd.getColumnCount();
			for (int i = 1; i <= columnSize; i++) {
				list.add(rmd.getColumnName(i));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, null, con);
		}
		return list;
	}

	/**
	 * <pre>
	 * 根据一个Connection对象和表名，获得表中所有列名和列的类型。</br>
	 * 返回对象格式为Map<列名，列类型>。
	 * </pre>
	 * 
	 * @param con
	 *            数据库连接对象。
	 * @param tableName
	 *            数据库表名。
	 * @return 返回一张表里的所有字段名和类型。
	 */
	public static Map<String, String> getColumnsType(Connection con, String tableName) {
		Map<String, String> map = new LinkedHashMap<String, String>();
		String sql = "select * from {0}";
		sql = StringUtilTools.StringFormat(sql, tableName);
		try {
			ps = con.prepareStatement(sql);
			ResultSetMetaData rmd = ps.getMetaData();

			int columnSize = rmd.getColumnCount();
			for (int i = 1; i <= columnSize; i++) {
				map.put(rmd.getColumnName(i), rmd.getColumnClassName(i));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, null, con);
		}
		return map;
	}

	/**
	 * <pre>
	 * 使用SQL，查询一个List<Map<String,Object>>对象。
	 * </pre>
	 * 
	 * @param con
	 *            connection对象。
	 * @param sql
	 *            sql语句
	 * @param values
	 *            SQL语句中的参数。
	 * @return 返回一个List对象。并且将结果集里的数据转换成Map对象形式。
	 */
	public static List<Map<String, Object>> executeQueryList(Connection con, String sql, Object... values) {
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}

			int totalSize = 0;
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				totalSize++;
			}
			info(clazz, "ResultSet Size = " + totalSize + "条");

			rs = ps.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
		return changeResult(rs);
	}

	/**
	 * <pre>
	 * 使用SQL，查询一个List<Map<String,Object>>对象。
	 * </pre>
	 * 
	 * @param con
	 *            connection对象。
	 * @param sql
	 *            sql语句
	 * @param values
	 *            SQL语句中的参数。
	 * @return 返回一个List对象。并且将结果集里的数据转换成Map对象形式。
	 */
	public static List<Map<String, Object>> executeQueryPageList(Connection con, String sql, Object... values) {
		return executeQueryPageList(con, sql, new Page().getPageNo(), new Page().getPageSize(), values);
	}

	/**
	 * <pre>
	 * 使用SQL，查询一个List<Map<String,Object>>对象。
	 * </pre>
	 * 
	 * @param con
	 *            connection对象。
	 * @param sql
	 *            sql语句
	 * @param pageNo
	 *            页号。
	 * @param pageSize
	 *            每页大小。
	 * @param values
	 *            SQL语句中的参数。
	 * @return 返回一个List对象。并且将结果集里的数据转换成Map对象形式。
	 */
	public static List<Map<String, Object>> executeQueryPageList(Connection con, String sql, int pageNo, int pageSize, Object... values) {
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}

			int totalSize = 0;
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				totalSize++;
			}
			info(clazz, "ResultSet Size = " + totalSize + "条");

			ps.setMaxRows(Page.getStartOfPage(pageNo, pageSize) + pageSize);
			rs = ps.executeQuery();

			if (Page.getStartOfPage(pageNo, pageSize) <= 0) {
				return changeResult(rs);
			} else {
				rs.absolute(Page.getStartOfPage(pageNo, pageSize));
				return changeResult(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
		return null;
	}

	/**
	 * <pre>
	 * 使用SQL，查询一个Page对象。
	 * </pre>
	 * 
	 * @param con
	 *            connection对象。
	 * @param sql
	 *            sql语句
	 * @param values
	 *            SQL语句中的参数。
	 * @return 返回一个Page对象。
	 */
	public static Page executeQueryPage(Connection con, String sql, Object... values) {
		return executeQueryPage(con, sql, new Page().getPageNo(), new Page().getPageSize(), values);
	}

	/**
	 * <pre>
	 * 使用SQL，查询一个Page对象。
	 * </pre>
	 * 
	 * @param con
	 *            connection对象。
	 * @param sql
	 *            sql语句
	 * @param pageNo
	 *            页号。
	 * @param pageSize
	 *            每页大小。
	 * @param values
	 *            SQL语句中的参数。
	 * @return 返回一个Page对象。
	 */
	public static Page executeQueryPage(Connection con, String sql, int pageNo, int pageSize, Object... values) {
		Page page = null;
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}

			int totalSize = 0;
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				totalSize++;
			}
			info(clazz, "ResultSet Size = " + totalSize + "条");

			ps.setMaxRows(Page.getStartOfPage(pageNo, pageSize) + pageSize);
			rs = ps.executeQuery();

			if (Page.getStartOfPage(pageNo, pageSize) <= 0) {
				page = new Page(Page.getStartOfPage(pageNo, pageSize), totalSize, pageSize, changeResult(rs));
			} else {
				rs.absolute(Page.getStartOfPage(pageNo, pageSize));
				page = new Page(Page.getStartOfPage(pageNo, pageSize), totalSize, pageSize, changeResult(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
		return page;
	}

	/**
	 * <pre>
	 * 将一个ResultSet封装成一个List<Map<String, Object>>结果集。</br>
	 * 每一条记录为一个Map<String, Object>，key为数据库中表的列名，value为这条记录中列名对应的值。</br>
	 * 多个Map<String, Object>放在List里，成为一个结果集。</br>
	 * 使用时，遍历List取得每个Map，从Map里使用get(key)方法获得结果。
	 * </pre>
	 * 
	 * @param rs
	 *            需要封装的ResultSet对象。
	 * @return List<Map<String, Object>> 返回的结果集。
	 */
	private static List<Map<String, Object>> changeResult(ResultSet rs) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			// 非常有用的
			ResultSetMetaData rsmd = rs.getMetaData();
			// 用法rs可以的到有多少列
			int columnNum = rsmd.getColumnCount();

			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int i = 1; i <= columnNum; i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, null, null);
		}
		return list;
	}

	/**
	 * <pre>
	 * 读取一个properties,并设置数据库连接的url，username，password。
	 * </pre>
	 * 
	 * @param filePath
	 *            properties文件路径
	 */
	private static void getPramert(String filePath) {
		Map<String, String> map = PropertiesUtilTools.readPropertiesFile(filePath);
		url = map.get("jdbc.url");
		userName = map.get("jdbc.username");
		password = map.get("jdbc.password");
	}

}
